package net.droiding.data;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;


public class DroidingDao {
	
	private static final String STM_INSERTPROVIDER = 
		"INSERT INTO providers "
		+"(pkey, name, location, latitude, longitude, zoomlevel, lastupdate) " 
		+ "VALUES (?, ?, ?, ?, ?, ?, ?);";
	
	private static final String STM_INSERTSTATION = 
		"INSERT INTO stations "
		+"(remoteid, key, pkey, location, latitude, longitude, bikes, slots, lastupdate, favorite) " 
		+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
	
	private static final String STM_UPDATESTATION = 
		"UPDATE stations "
		+"SET bikes = ?, slots = ?, lastupdate = ? " 
		+ "WHERE remoteid = ?;";
	
	private Context ctx = null;
	
	private DroidingDBHelper helper = null;
	
	public DroidingDao(Context ctx) {
		this.ctx = ctx;
		helper = new DroidingDBHelper(this.ctx);
	}
	
	public void insertProviders(Provider[] providers) {
		SQLiteDatabase db = helper.getWritableDatabase();
		SQLiteStatement stm = db.compileStatement(STM_INSERTPROVIDER);
		for (Provider prov : providers) {
			stm.clearBindings();
			stm.bindString(1, prov.key);
			stm.bindString(2, prov.name);
			stm.bindString(3, prov.location);
			stm.bindString(4, prov.latitude);
			stm.bindString(5, prov.longitude);
			stm.bindLong(6, prov.zoomLevel);
			stm.bindLong(7, prov.lastUpdate); 
			long id = stm.executeInsert();
			Log.d("Droiding", "new provider " + prov.key + " inserted ! id = " + id);
		}
		stm.close();
		db.close();
	}		

	public void insertStations(Station[] stations) {
		SQLiteDatabase db = helper.getWritableDatabase();
		SQLiteStatement stm = db.compileStatement(STM_INSERTSTATION);
		for (Station st : stations) {
			stm.clearBindings();
			stm.bindLong(1, st.remoteId);
			stm.bindString(2, st.key);
			stm.bindString(3, st.pkey);
			stm.bindString(4, st.location);
			stm.bindString(5, st.latitude);
			stm.bindString(6, st.longitude);
			stm.bindLong(7, st.bikes);
			stm.bindLong(8, st.slots);
			stm.bindLong(9, st.lastUpdate); 
			long id = stm.executeInsert();
			Log.d("Droiding", "new provider " + st.remoteId + " inserted ! id = " + id);
		}
		stm.close();
		db.close();
	}
	public void insertStation(Station st) {
		SQLiteDatabase db = helper.getWritableDatabase();
		SQLiteStatement stm = db.compileStatement(STM_INSERTSTATION);
		stm.clearBindings();
		stm.bindLong(1, st.remoteId);
		stm.bindString(2, st.key);
		stm.bindString(3, st.pkey);
		stm.bindString(4, st.location);
		stm.bindString(5, st.latitude);
		stm.bindString(6, st.longitude);
		stm.bindLong(7, st.bikes);
		stm.bindLong(8, st.slots);
		stm.bindLong(9, st.lastUpdate);
		stm.bindLong(10, st.favorite ? 1 : 0);
		long id = stm.executeInsert();
		Log.d("Droiding", "new station " + st.remoteId + " inserted ! id = " + id);
		stm.close();
		db.close();
	}
	public Station getStationByRemoteId(int remoteid) {
		SQLiteDatabase db = helper.getReadableDatabase();
		//TODO change to a preparedStatement
		Station result = null;
		Cursor c = db.query(DroidingDBHelper.STATIONS_TABLE_NAME, new String[] { "_id", "pkey", "key", "location", "latitude", "longitude",  "bikes", "slots", "lastupdate", "favorite"},  "remoteid = " + remoteid +"", null, null, null, null);
		if (c.moveToFirst()) {
			String pkey = c.getString(c.getColumnIndex("pkey"));
			String key = c.getString(c.getColumnIndex("key"));
			String location = c.getString(c.getColumnIndex("location"));
			String latitude = c.getString(c.getColumnIndex("latitude"));
			String longitude = c.getString(c.getColumnIndex("longitude"));
			int bikes = c.getInt(c.getColumnIndex("bikes"));
			int slots = c.getInt(c.getColumnIndex("slots"));
			int favorite = c.getInt(c.getColumnIndex("favorite"));
			long lastupdate = c.getLong(c.getColumnIndex("lastupdate"));
			result = new Station(remoteid, key, pkey, location, latitude, longitude, bikes, slots, lastupdate, (favorite != 0));
		}
		c.close();
        db.close();		
        return result;
	}
	public void updateStation(Station station) {
		SQLiteDatabase db = helper.getWritableDatabase();
		SQLiteStatement stm = db.compileStatement(STM_UPDATESTATION);
		stm.bindLong(1, station.bikes);
		stm.bindLong(2, station.slots);
		stm.bindLong(3, station.lastUpdate);
		stm.bindLong(4, station.remoteId);
		stm.execute();
		stm.close();
		db.close();
	}
	
//			query
//					.append("INSERT INTO providers (pkey, name, location, latitude, longitude, zoomlevel, lastupdate) ");
//			query.append("VALUES (");
			
//		}
//		DocumentBuilder builder = null;
//		Document doc = null;
//		try {
//			builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
//			doc = builder.parse(new ByteArrayInputStream(xml));
//			NodeList providers = doc.getElementsByTagName("provider");
//			for (int i = 0; i < providers.getLength(); i++) {
//				Element provider = (Element) providers.item(i);
//				String key = provider.getAttribute("key");
//				String name = provider.getAttribute("name");
//				String location = provider.getAttribute("location");
//				String latitude = provider.getAttribute("lat");
//				String longitude = provider.getAttribute("lng");
//				String zoom = provider.getAttribute("zoom");
//				String lastupdate = provider.getAttribute("lastupdate");
//
//				StringBuffer query = new StringBuffer();
//				query
//						.append("INSERT INTO providers (pkey, name, location, latitude, longitude, zoomlevel, lastupdate) ");
//				query.append("VALUES (");
//				query.append("'").append(key).append("', ");
//				query.append("'").append(name).append("', ");
//				query.append("'").append(location).append("', ");
//				query.append("'").append(latitude).append("', ");
//				query.append("'").append(longitude).append("', ");
//				query.append("").append(zoom).append(", ");
//				query.append(lastupdate).append(" );");
//				db.execSQL(query.toString());
//				// data.add(key + " - " + name);
//			}
//			db.close();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//	}
	
	public Provider getProvider(String pkey) {
		SQLiteDatabase db = helper.getReadableDatabase();
		//TODO change to a preparedStatement
		Cursor c = db.query(DroidingDBHelper.PROVIDERS_TABLE_NAME, new String[] { "_id", "name", "location", "latitude", "longitude",  "zoomlevel", "lastupdate"},  "pkey = '" + pkey +"'", null, null, null, null);
		c.moveToFirst();
		String name = c.getString(c.getColumnIndex("name"));
		String location = c.getString(c.getColumnIndex("location"));
		String latitude = c.getString(c.getColumnIndex("latitude"));
		String longitude = c.getString(c.getColumnIndex("longitude"));
		int zoomlevel = c.getInt(c.getColumnIndex("zoomlevel"));
		long lastupdate = c.getLong(c.getColumnIndex("lastupdate"));
        c.close();
        db.close();		
        return new Provider(pkey, name, location, latitude, longitude, zoomlevel, lastupdate);
	}
	
	public Station[] getStations(String pkey) {
		SQLiteDatabase db = helper.getReadableDatabase();
		//TODO change to a preparedStatement
		Cursor c = db.query(DroidingDBHelper.STATIONS_TABLE_NAME, 
				new String[] { "_id", "remoteid", "key", "location", "latitude", "longitude",  "bikes", "slots", "lastupdate", "favorite"},  
				"pkey = '" + pkey +"'", null, null, null, null);
		Station[] stations = null;
		int i = 0;
		while (c.moveToNext()) {
			if (stations == null)
				stations = new Station[c.getCount()];
			int remoteId = c.getInt(c.getColumnIndex("remoteid"));
			String key = c.getString(c.getColumnIndex("key"));
			String location = c.getString(c.getColumnIndex("location"));
			String latitude = c.getString(c.getColumnIndex("latitude"));
			String longitude = c.getString(c.getColumnIndex("longitude"));
			int bikes = c.getInt(c.getColumnIndex("bikes"));
			int slots = c.getInt(c.getColumnIndex("slots"));
			long lastupdate = c.getLong(c.getColumnIndex("lastupdate"));
			int favorite = c.getInt(c.getColumnIndex("favorite"));
			stations[i] = new Station(remoteId, key, pkey, location, latitude, longitude, bikes, slots, lastupdate, (favorite != 0));
			i++;
		}
        c.close();
        db.close();		
        return stations;
	}
	
	public Cursor createProvidersCursor() {
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.query(DroidingDBHelper.PROVIDERS_TABLE_NAME, new String[] { "_id", "pkey", "name",
				"location" }, null, null, null, null, null);
		c.moveToFirst();
		db.close();
		return c;
	}
	public Cursor createStationsCursor(String pkey) {
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.query(DroidingDBHelper.STATIONS_TABLE_NAME, new String[] { "_id", "location", "favorite" }, 
				"pkey = '" + pkey +"'", null, null, null, "location asc");
		c.moveToFirst();
		db.close();
		return c;		
	}
	class DroidingDBHelper extends SQLiteOpenHelper {

		private static final String DATABASE_NAME = "droiding.db";
		private static final int DATABASE_VERSION = 11;
		public static final String PROVIDERS_TABLE_NAME = "providers";
		public static final String STATIONS_TABLE_NAME = "stations";
		
		public DroidingDBHelper(Context context) {
	        super(context, DATABASE_NAME, null, DATABASE_VERSION);
	    }
	    @Override
	    public void onCreate(SQLiteDatabase db) {
	        db.execSQL("CREATE TABLE " + PROVIDERS_TABLE_NAME + " ("
	        		+ "_id INTEGER PRIMARY KEY,"
	                + "pkey TEXT,"
	                + "name TEXT,"
	                + "location TEXT,"
	                + "latitude TEXT,"
	                + "longitude TEXT,"
	                + "zoomlevel INTEGER,"
	                + "lastupdate INTEGER "
	                + ");");
	        db.execSQL("CREATE TABLE " + STATIONS_TABLE_NAME + " ("
	                + "_id INTEGER PRIMARY KEY,"
	                + "remoteid INTEGER,"
	                + "key TEXT,"
	                + "pkey TEXT,"
	                + "location TEXT,"
	                + "latitude TEXT,"
	                + "longitude TEXT,"
	                + "bikes INTEGER,"
	                + "slots INTEGER,"
	                + "lastupdate INTEGER,"
	                + "favorite INTEGER"
	                + ");");	     
	/*
	 *  // Given a local time of 10am, get the time in Japan
	    // Create a Calendar object with the local time zone
	    Calendar local = new GregorianCalendar();
	    local.set(Calendar.HOUR_OF_DAY, 10);               // 0..23
	    local.set(Calendar.MINUTE, 0);
	    local.set(Calendar.SECOND, 0);
	    
	    // Create an instance using Japan's time zone and set it with the local UTC
	    Calendar japanCal = new GregorianCalendar(TimeZone.getTimeZone("Japan"));
	    japanCal.setTimeInMillis(local.getTimeInMillis());
	    
	    // Get the foreign time
	    int hour = japanCal.get(Calendar.HOUR);            // 3
	    int minutes = japanCal.get(Calendar.MINUTE);       // 0
	    int seconds = japanCal.get(Calendar.SECOND);       // 0
	    boolean am = japanCal.get(Calendar.AM_PM) == Calendar.AM; //true
	    
	    
	    // Given a time of 10am in Japan, get the local time
	    japanCal = new GregorianCalendar(TimeZone.getTimeZone("Japan"));
	    japanCal.set(Calendar.HOUR_OF_DAY, 10);            // 0..23
	    japanCal.set(Calendar.MINUTE, 0);
	    japanCal.set(Calendar.SECOND, 0);
	    
	    // Create a Calendar object with the local time zone and set
	    // the UTC from japanCal
	    local = new GregorianCalendar();
	    local.setTimeInMillis(japanCal.getTimeInMillis());
	    
	    // Get the time in the local time zone
	    hour = local.get(Calendar.HOUR);                   // 5
	    minutes = local.get(Calendar.MINUTE);              // 0
	    seconds = local.get(Calendar.SECOND);              // 0
	    am = local.get(Calendar.AM_PM) == Calendar.AM;     // false
	        
	 */
	    }

	    @Override
	    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	        Log.w("DroidingDBHelper", "Upgrading database from version " + oldVersion + " to "
	                + newVersion + ", which will destroy all old data");
	        db.execSQL("DROP TABLE IF EXISTS " + STATIONS_TABLE_NAME);
	        db.execSQL("DROP TABLE IF EXISTS " + PROVIDERS_TABLE_NAME);
	        onCreate(db);
	    }
	}



}
